package com.imut.lagain.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.imut.lagain.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 用户数据访问层
 */
@Mapper
public interface UserRepository extends BaseMapper<User> {
    
    /**
     * 根据openid查询用户
     * @param openid 微信openid
     * @return 用户信息
     */
    @Select("SELECT * FROM user WHERE openid = #{openid} AND is_deleted = 0")
    User findByOpenid(@Param("openid") String openid);
    
    /**
     * 根据unionid查询用户
     * @param unionid 微信unionid
     * @return 用户信息
     */
    @Select("SELECT * FROM user WHERE unionid = #{unionid} AND is_deleted = 0")
    User findByUnionid(@Param("unionid") String unionid);
    
    /**
     * 根据手机号查询用户
     * @param phone 手机号
     * @return 用户信息
     */
    @Select("SELECT * FROM user WHERE phone = #{phone} AND is_deleted = 0 ORDER BY create_time DESC LIMIT 1")
    User findByPhone(@Param("phone") String phone);
    
    /**
     * 查询活跃用户列表
     * @param page 分页参数
     * @return 活跃用户分页列表
     */
    @Select("SELECT * FROM user WHERE is_active = 1 AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<User> findActiveUsers(Page<User> page);
    
    /**
     * 查询指定时间范围内注册的用户
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 用户列表
     */
    @Select("SELECT * FROM user WHERE create_time BETWEEN #{startTime} AND #{endTime} AND is_deleted = 0 ORDER BY create_time DESC")
    List<User> findUsersByCreateTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计活跃用户数量
     * @return 活跃用户数量
     */
    @Select("SELECT COUNT(*) FROM user WHERE is_active = 1 AND is_deleted = 0")
    Long countActiveUsers();
    
    /**
     * 统计总用户数量
     * @return 总用户数量
     */
    @Select("SELECT COUNT(*) FROM user WHERE is_deleted = 0")
    Long countTotalUsers();
    
    /**
     * 统计指定时间范围内注册的用户数量
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 用户数量
     */
    @Select("SELECT COUNT(*) FROM user WHERE create_time BETWEEN #{startTime} AND #{endTime} AND is_deleted = 0")
    Long countUsersByCreateTime(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 更新用户活跃状态
     * @param userId 用户ID
     * @param isActive 是否活跃
     * @return 影响行数
     */
    @Update("UPDATE user SET is_active = #{isActive}, update_time = NOW() WHERE id = #{userId}")
    int updateActiveStatus(@Param("userId") Long userId, @Param("isActive") Boolean isActive);
    
    /**
     * 批量更新用户活跃状态
     * @param userIds 用户ID列表
     * @param isActive 是否活跃
     * @return 影响行数
     */
    @Update("<script>" +
            "UPDATE user SET is_active = #{isActive}, update_time = NOW() WHERE id IN " +
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchUpdateActiveStatus(@Param("userIds") List<Long> userIds, @Param("isActive") Boolean isActive);
    
    /**
     * 根据昵称模糊查询用户
     * @param nickname 昵称关键字
     * @param page 分页参数
     * @return 用户分页列表
     */
    @Select("SELECT * FROM user WHERE nickname LIKE CONCAT('%', #{nickname}, '%') AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<User> findUsersByNickname(@Param("nickname") String nickname, Page<User> page);
    
    /**
     * 检查openid是否存在
     * @param openid 微信openid
     * @return 是否存在
     */
    @Select("SELECT COUNT(*) > 0 FROM user WHERE openid = #{openid} AND is_deleted = 0")
    boolean existsByOpenid(@Param("openid") String openid);
    
    /**
     * 检查unionid是否存在
     * @param unionid 微信unionid
     * @return 是否存在
     */
    @Select("SELECT COUNT(*) > 0 FROM user WHERE unionid = #{unionid} AND is_deleted = 0")
    boolean existsByUnionid(@Param("unionid") String unionid);
    
    /**
     * 检查手机号是否存在
     * @param phone 手机号
     * @return 是否存在
     */
    @Select("SELECT COUNT(*) > 0 FROM user WHERE phone = #{phone} AND is_deleted = 0")
    boolean existsByPhone(@Param("phone") String phone);
    
    /**
     * 查找不活跃用户（超过指定时间未登录）
     * @param lastActiveTime 最后活跃时间
     * @return 不活跃用户列表
     */
    @Select("SELECT * FROM user WHERE last_login_time < #{lastActiveTime} AND is_deleted = 0 ORDER BY last_login_time ASC")
    List<User> findInactiveUsers(@Param("lastActiveTime") LocalDateTime lastActiveTime);
    
    /**
     * 统计用户数量（支持多条件）
     * @param isActive 是否活跃
     * @param isDeleted 是否删除
     * @return 用户数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM user WHERE 1=1" +
            "<if test='isActive != null'> AND is_active = #{isActive}</if>" +
            "<if test='isDeleted != null'> AND is_deleted = #{isDeleted}</if>" +
            "</script>")
    Long countUsers(@Param("isActive") Boolean isActive, @Param("isDeleted") Boolean isDeleted);
    
    /**
     * 根据ID查询用户（包含所有字段）
     * @param id 用户ID
     * @return 用户信息
     */
    @Select("SELECT * FROM user WHERE id = #{id} AND is_deleted = 0")
    User findUserById(@Param("id") Long id);
    
    /**
     * 根据昵称模糊查询用户
     * @param nickname 昵称关键字
     * @param page 分页参数
     * @return 用户分页列表
     */
    @Select("SELECT * FROM user WHERE nickname LIKE CONCAT('%', #{nickname}, '%') AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<User> findByNicknameLike(@Param("nickname") String nickname, Page<User> page);
    
    /**
     * 统计指定时间范围内的活跃用户数量
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 活跃用户数量
     */
    @Select("SELECT COUNT(*) FROM user WHERE is_active = 1 AND is_deleted = 0 AND update_time BETWEEN #{startTime} AND #{endTime}")
    Long countActiveUsersByTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
}
